Plan i statistika izvršenja SQL komandi

 

Plan i statistika izvršenja SQL komandi 1

Kako mogu da vidim plan i statistiku izvršenja komandi koristeći SQL*Plus?. 1

Test infrastructure. 1

Instalacija infrastructure. 1

ANALIZA UPITA PO ZAHTEVU.. 1

AUTOMATSKA ANALIZA UPITA - AUTOTRACE SESIJE. 3

 

 

Kako mogu da vidim plan i statistiku izvršenja komandi koristeći SQL*Plus?

 

Test infrastructure

 

Probati postoji li infrastruktura za statistiku npr. SQL> set autotrace on

Primer greske v10.2

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

 

Instalacija infrastructure

 

1.Kao korisnik sys startovati skript @%ORACLE_HOME%\sqlplus\admin\plustrce.sql

2.Grantovati rolu plustrace korisniku npr. SQL>GRANT plustrace TO SCOTT;

3.Kreirati tabelu PLAN_TABLE kao korisnik koji vrsi analizu: SQL>@%ORACLE_HOME%\RDBMS\ADMIN\utlxplan.sql

 

ANALIZA UPITA PO ZAHTEVU

 

set ORACLE_HOME=D:\oracle\product\10.2.0\db_1

 

SQL> explain plan for select * from emp;

 

Explained.

 

SQL> @%ORACLE_HOME%\rdbms\admin\utlxpls.sql

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------

Plan hash value: 3956160932

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

8 rows selected.

 

Umesto skripta moze se koristiti i paket DBMS_XPLAN:

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

Plan hash value: 3956160932

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

SQL>truncate table plan_table

 

--Paralell

SQL> explain plan for select * from emp1;

 

SQL> @%ORACLE_HOME%\rdbms\admin\utlxplp.sql

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------

Plan hash value: 3728111555

 

--------------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |    14 |   518 |     2   (0)| 00:00:01 |        |      |         |

|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR |          |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |         |

|   4 |     TABLE ACCESS FULL| EMP1     |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |         |

--------------------------------------------------------------------------------------------------------------

 

AUTOMATSKA ANALIZA UPITA - AUTOTRACE SESIJE

 

Kada se SQL*Plus parametru AUTOTRACE dodeli vrednost ON analiza se vrsi kod svakog upita.

 

SQL> set autotrace on

SQL> select * from emp1;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

14 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3728111555

 

--------------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |    14 |   518 |     2   (0)| 00:00:01 |        |      |         |

|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR |          |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |         |

|   4 |     TABLE ACCESS FULL| EMP1     |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |         |

--------------------------------------------------------------------------------------------------------------

 

 

Statistics

----------------------------------------------------------

         12  recursive calls

          0  db block gets

         14  consistent gets

          0  physical reads

          0  redo size

       1357  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         14  rows processed